﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using HNQXYJ.Model;
using System.Data;
using System.Data.SqlClient;

namespace HNQXYJ.DAL
{
    public class CityDAL
    {
        /// <summary>
        /// 返回城市的数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public  DataTable GetCityTable(string procName,params SqlParameter[] paras)
        {
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            return dt;
        }

        /// <summary>
        /// 将一个城市列数据转化为一个对象模型
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private  HCity ToModel(DataRow dr)
        {
            HCity city = new HCity();
            city.ID = Convert.ToInt32(dr["Id"]);
            city.Name = dr["Name"].ToString();
           // city.ParentCode = dr["ParentCode"].ToString();
            city.SelfCode = dr["SelfCode"].ToString();
            city.DateAdd = Convert.ToDateTime(dr["DateAdd"]);
            city.Lng = Convert.ToDouble(dr["Lng"]);
            city.Lat = Convert.ToDouble(dr["Lat"]);
            return city;

        }

        /// <summary>
        /// 以对象列表的形式返回City中的数据
        /// </summary>
        /// <returns></returns>
        public List<HCity> GetCitiesListAll()
        {
            string procName = "CityGetDataAll";
            DataTable dt = GetCityTable(procName);
            List<HCity> list = new List<HCity>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(ToModel(row));
            }
            return list;
        }


        /// <summary>
        /// 根据获取的（从session中)权限码（城市表的selfcode）来回去某个城市的基本信息
        /// </summary>
        /// <param name="selfCode">selfCode</param>
        /// <returns>HCity</returns>
        public HCity GetCityBySelfCode(string selfCode)
        {
            string procName = "CityGetDataBySelfCode";
            SqlParameter[] paras = { 
                                   new SqlParameter("@SelfCode",SqlDbType.VarChar,100)
                                   };
            paras[0].Value = selfCode;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            if (dt.Rows.Count == 1)
            {
                DataRow dr = dt.Rows[0];
                HCity city = ToModel(dr);
                return city;
            }
            else
            {
                return null;
            }
 
        }

        /// <summary>
        /// 添加城市
        /// </summary>
        /// <param name="city"></param>
        /// <returns></returns>
        public int AddCity(HCity city)
        {
            string procName = "CityInsertData";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Name",SqlDbType.NVarChar,50),
                                   new SqlParameter("@ParentCode",SqlDbType.VarChar ,100),
                                   new SqlParameter("@SelfCode",SqlDbType.VarChar,100),
                                   new SqlParameter("@DateAdd",SqlDbType.DateTime),
                                   new SqlParameter("@Lng",SqlDbType.Float),
                                   new SqlParameter("@Lat",SqlDbType.Float)                                
                                   };
            paras[0].Value = city.Name;
            paras[1].Value = city.ParentCode;
            paras[2].Value = city.SelfCode;
            paras[3].Value = city.DateAdd;
            paras[4].Value = city.Lng;
            paras[5].Value = city.Lat;

            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;

        }

        /// <summary>
        /// 根据id删除城市
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteCityById(int id)
        {
            string procName = "CityDeleteById";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Id",SqlDbType.Int )
                                   };
            paras[0].Value = id;
            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;
        }

        /// <summary>
        /// 根据id获取一个city对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public HCity GetCityById(int id)
        {
            string procName = "CityGetDataById";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Id",SqlDbType.Int)
                                   };
            paras[0].Value = id;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            DataRow dr = dt.Rows[0];
            HCity city = ToModel(dr);
            return city;
        }

        /// <summary>
        /// 更新某个城市的数据
        /// </summary>
        /// <param name="city"></param>
        /// <returns></returns>
        public int UpdateCity(HCity city)
        {
            string procName = "CityUpdateData";
            SqlParameter[] paras = { 
                                   new SqlParameter ("@Id",SqlDbType.Int),
                                   new SqlParameter("@Name",SqlDbType.NVarChar,50),
                                   new SqlParameter("@SelfCode",SqlDbType.VarChar,100),
                                   new SqlParameter("@Lng",SqlDbType.Float),
                                   new SqlParameter ("@Lat",SqlDbType.Float)
                                   };
            paras[0].Value = city.ID;
            paras[1].Value = city.Name;
            paras[2].Value = city.SelfCode;
            paras[3].Value = city.Lng;
            paras[4].Value = city.Lat;
            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;
        }
    }
}